package com.googlecode.bip.dao.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayDeque;
import java.util.Deque;
import java.util.List;
import javax.naming.NamingException;
//import org.apache.log4j.Logger;
import com.googlecode.bip.dao.TopicDAO;
import com.googlecode.bip.dao.utils.ListableInstance;
import com.googlecode.bip.entity.ITopic;
import com.googlecode.bip.entity.IllegalIdLocalException;
import com.googlecode.bip.entity.impl.Local;
import com.googlecode.bip.entity.impl.Topic;
import cx.ath.choisnet.sql.SQLTools;

/**
 * MySQL implementation of {@link TopicDAO}
 */
public class MySQLTopicDAO
    extends MySQLDAOBuilder<ITopic>
        implements TopicDAO, ListableInstance<ITopic>
{
    private static final long serialVersionUID = 1L;
    //private static final transient Logger logger = Logger.getLogger( MySQLTopicDAO.class );

    public MySQLTopicDAO()
    {
        // empty
    }

    @Override //ListableInstance<ITopic>
    public ITopic newInstance( final ResultSet rSet )
        throws SQLException
    {
        return new Topic().set( rSet );
    }

    @Override
    public ITopic findTopic( final int idtopic, final Local local )
        throws NamingException, SQLException
    {
        return findTopic( idtopic, local.getIdLocal() );
    }

    @Override
    public ITopic findTopic( final int idtopic, final String idlocal )
        throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `idtopic`="
            + idtopic
            + " AND `idlocal`='"
            + idlocal
            +"';";

        return getElement( sql );
    }

    @Override
    public ITopic findTopic( final String topicname, final String idlocal )
            throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `topicname`='"
            + SQLTools.parseFieldValue( topicname )
            + "' AND `idlocal`='"
            + idlocal
            +"';";

        return getElement( sql );
    }

    @Override
    public List<ITopic> findTopics( final String topicname, final String idlocal )
            throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `topicname`='"
            + SQLTools.parseFieldValue( topicname )
            + "' AND `idlocal`='"
            + idlocal
            +"';";

        return getList( sql );
    }

    @Override
    public List<ITopic> findTopics( final int idtopic )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `idtopic`="
            + idtopic
            + ";";

        return getList( sql );
    }

    @Override
    public List<ITopic> getAllTopics()
        throws SQLException, NamingException
    {
       final String sql = "SELECT * FROM `topicsview`;";

       return getList( sql );
    }

    @Override
    public List<ITopic> getRootTopics()
        throws SQLException, NamingException
    {
        return getSubTopics( 0 );
    }

    @Override
    public List<ITopic> getSubTopics( final int parentidtopic )
        throws SQLException, NamingException
    {
       final String sql = "SELECT * FROM `topicsview` WHERE `parentidtopic`="
                           + parentidtopic
                           + " ORDER BY `order`;";

       return getList( sql );
    }

    @Override
    public ITopic insertTopic(
            final int    parentIdTopic,
            final String idlocal,
            final String topicName,
            final String topicDescription
            )
        throws NamingException, SQLException, IllegalIdLocalException
    {
        final String sql = "CALL add_topic( '"
            + MySQLLocalDAO.static_getValidIdLocal( idlocal )
            + "', '"
            + SQLTools.parseFieldValue( topicName )
            + "', '"
            + SQLTools.parseFieldValue( topicDescription )
            + "', "
            + parentIdTopic
            + " );";

        return executeQuery( sql );
    }

    @Override
    public ITopic insertTopic(
            final ITopic topicBase,
            final String idlocal,
            final String name,
            final String description
            )
        throws  SQLException,
                NamingException,
                IllegalIdLocalException
    {
        final String sql = "CALL update_topic( "
            + topicBase.getIdTopic()
            + ", '"
            + MySQLLocalDAO.static_getValidIdLocal( idlocal )
            + "', '"
            + SQLTools.parseFieldValue( name )
            + "', '"
            + SQLTools.parseFieldValue( description )
            + "', "
            + topicBase.getOrder()
            + ", "
            + topicBase.getParentIdTopic()
            + ", "
            + topicBase.getIsActive()
            + " );";

        return executeQuery( sql );
    }

    @Override
    public boolean updateTopic( final ITopic itopic )
        throws NamingException, SQLException, IllegalIdLocalException
    {
        final String sql = "CALL update_topic( "
           + itopic.getIdTopic()
           + ", '"
           + MySQLLocalDAO.static_getValidIdLocal( itopic.getIdLocal() )
           + "', '"
           + SQLTools.parseFieldValue( itopic.getName() )
           + "', '"
           + SQLTools.parseFieldValue( itopic.getDescription() )
           + "', "
           + itopic.getOrder()
           + ", "
           + itopic.getParentIdTopic()
           + ", "
           + itopic.getIsActive()
           + " );";

       doUpdate( sql );

       return true;
    }

    @Override
    public boolean deleteTopic( final int idtopic )
        throws NamingException, SQLException
    {
        final String sql = "DELETE FROM `topics` WHERE `idtopic`='"
            + idtopic + "';";

//        if( logger.isDebugEnabled() ) {
//            logger.debug( "deleteTopic: sql = " + sql );
//            }

        return doUpdate( sql ) == 1;
    }

    @Override
    public boolean deleteTopicLocal( final int idtopic, final String idlocal )
            throws NamingException, SQLException
    {
        final String sql = "DELETE FROM `topics_local` WHERE `idtopic`='"
                + idtopic
                + "' AND `idlocal`='"
                + idlocal
                + "';";

//        if( logger.isDebugEnabled() ) {
//            logger.debug( "deleteTopicLocal: sql = " + sql );
//            }

        return doUpdate( sql ) == 1;
    }

    @Override
    public int deleteTopicLocals( final int idtopic )
        throws NamingException, SQLException
    {
        final String sql = "DELETE FROM `topics_local` WHERE `idtopic`='"
            + idtopic
            + "';";

//        if( logger.isDebugEnabled() ) {
//            logger.debug( "deleteTopicLocals: sql = " + sql );
//            }

        return doUpdate( sql );
    }

    @Override
    public void packOrderTopics() throws NamingException, SQLException
    {
        final Deque<Integer> topicIdQueue = new ArrayDeque<Integer>();

        topicIdQueue.addFirst( new Integer( 0 ) );

        do {
            try {
                List<ITopic> topicList = packOrderTopics( topicIdQueue.poll() );

                for( ITopic topic : topicList ) {
                    topicIdQueue.addFirst( new Integer( topic.getIdTopic() ) );
                    }
                }
            catch( IllegalIdLocalException e ) {
                throw new RuntimeException( e );
                }

            } while( topicIdQueue.size() > 0 );
    }

    private List<ITopic> packOrderTopics( final int parentidtopic )
        throws SQLException, NamingException, IllegalIdLocalException
    {
        final List<ITopic>  topicList   = getSubTopics( parentidtopic );
        int                 order       = 0;

        for( ITopic topic : topicList ) {
            if( topic.getOrder() != order ) {
                topic.setOrder( order );

                updateTopic( topic );
                }

            order++;
            }

        return topicList;
    }

}
